/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mx.ipn.escuela.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import mx.ipn.escuela.modelo.Materia;

/**
 *
 * @author AJC
 */
public class MateriaDao {

    public List<Materia> lista() {
        List<Materia> materias = new ArrayList<Materia>();
        Connection conn = null;
        try {
            conn = obtieneConexion();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from materia");
            while (rs.next()) {
                Materia materia = new Materia();
                materia.setId(rs.getLong("id"));
                materia.setClave(rs.getString("clave"));
                materia.setNombre(rs.getString("nombre"));
                materia.setDescripcion(rs.getString("descripcion"));
                materia.setCreditos(rs.getInt("creditos"));
                materias.add(materia);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return materias;
    }

    public Materia obtiene(Long id) {
        Materia materia = null;
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("select * from materia where id = ?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                materia = new Materia();
                materia.setId(rs.getLong("id"));
                materia.setClave(rs.getString("clave"));
                materia.setNombre(rs.getString("nombre"));
                materia.setDescripcion(rs.getString("descripcion"));
                materia.setCreditos(rs.getInt("creditos"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return materia;
    }

    public Materia crea(Materia materia) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("insert into "
                    + "materia(clave,nombre,descripcion,creditos)"
                    + "values(?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, materia.getClave());
            ps.setString(2, materia.getNombre());
            ps.setString(3, materia.getDescripcion());
            ps.setInt(4, materia.getCreditos());

            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                materia.setId(rs.getLong(1));
            } else {
                throw new RuntimeException("No se pudieron obtener "
                        + "las llaves generadas");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return materia;
    }

    public Materia actualiza(Materia materia) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("update materia "
                    + "set nombre = ?, descripcion = ?, creditos = ? where id = ?");
            ps.setString(1, materia.getNombre());
            ps.setString(2, materia.getDescripcion());
            ps.setInt(3, materia.getCreditos());
            ps.setLong(4, materia.getId());

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return materia;
    }

    public void elimina(Long id) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("delete from materia where id = ?");
            ps.setLong(1, id);

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MateriaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
    }

    private Connection obtieneConexion() throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///escuela", "ipn", "ipn00");
        return conn;
    }
}

